# -*- coding:UTF-8 -*-
# 读取case文件
import xlrd
# import xlwt
import os
import logging
from openpyxl import load_workbook

class request_xl(object):
    def __init__(self, xl_FileName):
        self.xl_FileName = xl_FileName
        self.data_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        self.data = xlrd.open_workbook(r"%s/data/%s" % (self.data_path, self.xl_FileName))
        # self.log = log
        # self.log.info("xlsx path === %s\data\%s" % (self.data_path, self.xl_FileName))

    def get_xl(self, sheetName, ApiPurpose):
        try:
            # 读取swagger表格的每一个sheet页
            for sheet_name in self.data.sheet_names():
                if sheet_name == sheetName:
                    # 获取的sheet 名称
                    table = self.data.sheet_by_name(sheet_name)
                    # 获取该sheet的行数
                    nrows = table.nrows
                    # 获取该sheet的列数
                    # ncols = table.ncols
                    for i in range(1, nrows):
                        # 使用行列索引，第1列为API Purpose，注：从0开始算
                        API_Purpose = table.row(i)[1].value
                        if API_Purpose == ApiPurpose:
                            self.i = i
                            case = table.row_values(i)
                            # self.log.info('test case ========%s' % case)
                            return case
        except Exception as e:
            logging.error("Get xlsx error ====== %s" % e)

    # xlwt只能新建不能修改原excel
    # def set_xl(self, caseFileName, sheetName, ApiPurpose, values):
    #     try:
    #         self.log.info("Set excel")
    #         self.get_xl(sheetName, ApiPurpose)
    #         # 创建一个workbook 设置编码
    #         workbook = xlwt.Workbook(encoding='utf-8')
    #         # 创建一个worksheet
    #         worksheet = workbook.add_sheet(sheetName, cell_overwrite_ok=True)
    #         worksheet.write(self.i, 6, values)
    #         self.log.info('Write (%s, %s, %s)' % (self.i, 6, values))
    #         workbook.save("%s\data\%s" % (self.data_path, caseFileName))
    #     except Exception as e:
    #         self.log.error("Set xlsx error ====== %s" % e)

    def update_xl(self, caseFileName, sheetName, ApiPurpose, values):
        try:
            # self.log.info("Update excel")
            logging.info("Update excel")
            self.get_xl(sheetName, ApiPurpose)
            # -*- coding: utf-8 -*-
            file_home = "%s\data\%s" % (self.data_path, caseFileName)
            wb = load_workbook(filename=file_home)
            ws = wb[sheetName]
            rowNum = self.i + 1
            ws['G%s' % rowNum] = values
            wb.save(file_home)
            logging.info('Update (%s, %s, %s)' % (self.i, 6, values))
        except Exception as e:
            logging.error("Set xlsx error ====== %s" % e)

